ICTSC Tech Blog

ING データベースに入れない！

ICTSC2022

概要

新規プロダクトの作成にあたり、MySQLサーバーを構築することになった。
先輩から「取り敢えずサクッと mysql-server を入れて雑にユーザー作っておいたよ」と連絡があり、先輩がMySQLサーバーの構築までは実施してくれたらしい。

しかし、手元のPCから接続しようとしたところ、何故かMySQLサーバーに接続できない。
PCからServer上のMySQLサーバーにログインできるようにしてほしい。

なお、近頃社内ではセキュリティに関するルールが厳しくなっており、脆弱なセキュリティ設定を行った場合は数時間で社内CSIRTから警告が届く。
別プロダクトで使用するマシンにセキュリティホールを作った日には、始末書どころでは済まされない。
このため、セキュリティの担保にも気を配らなければならない。

前提条件

  • PCのMySQLクライアントからだけでなくServer上で実行するWebサーバー(nginx等)からもMySQLサーバーに接続することがある
  • 「先輩」によってMySQLサーバーに作成されたユーザーのログイン情報は以下のとおり
    • ユーザー名: user
    • パスワード: ictsc2022
  • 社内ネットワークの構成は以下のとおり
  • PCと同様に 172.16.1.0/24 のネットワークに存在する他のホストからもServer上のMySQLにログインすることがある
  • 172.16.2.0/24 のネットワークにはServer以外にも別プロダクトのホストが存在する

制約

  • MySQLサーバーにおいて、ユーザーを作成または削除しない
  • 何らかのセキュリティ設定を変更する場合は報告書(解答)に明記する
    • セキュリティを強化する場合も明記する
    • セキュリティ設定を解除する場合は必要最低限に留める
  • 解答に係る通信は 172.16.0.0/16 のネットワークで完結する
    • 作業の際に 192.168.5.0/24 を使用することは可能
    • 解答の際に 192.168.5.0/24 のネットワークの存在は無視できる
  • 各端末を再起動しても終了状態が維持される

初期状態

  • PCから $ mysql -h 172.16.2.128 -u user -pictsc2022 をしてもログインできない。

終了状態

  • PCから $ mysql -h 172.16.2.128 -u user -pictsc2022 をするとログインに成功する。
  • Serverから $ mysql -u user -pictsc2022 をするとログインに成功する。
  • 前提条件を踏まえたセキュリティ設定がなされている

解説

MySQLサーバーに対し、MySQLサーバーが動作しているホストからはログインできるのに、外部ホストからはログインできないという問題です。
この問題は次の4つの設定ミスが複合して発生しています。

  • Routerに設定されたfirewallにてTCP3306の通信が禁止されている
  • ServerのufwによりTCP3306への通信が禁止されている
  • MySQLプロセスがlocalhostのみLISTENしている
  • userへのログインがlocalhostからのみ許可されている

このため、これら4つ全てを修正することで、ログインが可能となります。

想定していた解法

途中経路のルーターに設定されたfirewallにてTCP3306の通信が禁止されている

Routerにおいて、eth1 、即ちPCからのパケットについて client-in というルールによるファイアウォールが適用されています。
セキュリティ面を度外視すれば、ファイアウォールの設定を削除すればよいのですが、制約に「セキュリティ設定を解除する場合は必要最低限に留める」とあるため、全ての設定を削除することはできません。
ここでは、MySQLに用いるTCP3306の通信を許可するルールを client-in に追加することが適切です。
次のようにコマンドを実行するとルールの追加が可能です。

  • set firewall name client-in rule 6 action accept
  • set firewall name client-in rule 6 destination port mysql
  • set firewall name client-in rule 6 action protocol tcp

ServerのufwによりTCP3306への通信が禁止されている

Serverではufwが動作しており、初期状態ではOpenSSH、即ちTCP22のみ通信が許可されています。
ここでも、セキュリティ面を度外視すれば、sudo ufw disable などとしてufwを停止すればよいのですが、制約に「セキュリティ設定を解除する場合は必要最低限に留める」とあるため、ufwを停止することはできません。

このため、ufwにTCP3306への接続を許可するルールを追加する必要があります。
sudo ufw allow mysql のようにコマンドを実行して sudo ufw reload とするとTCP3306への通信が許可されます。

MySQLプロセスがlocalhostのみLISTENしている

(少なくとも)Ubuntu20.04において、MySQLをaptでインストールした直後の設定では、 127.0.0.1:3306 のみをLISTENしています。
このため、locahost以外からMySQLプロセスに接続することができません。
Serverにおいて /etc/mysql/mysql.conf.d/mysqld.cnf のbind-address を 0.0.0.0 に変更した上で、 sudo systemctl restart mysql のようにコマンドを実行してMySQLサーバーを再起動することでlocalhost以外からの接続を受け入れるようになります。

userへのログインがlocalhostからのみ許可されている

先輩が雑に作ったユーザーは 'user'@'localhost' であり、localhostからのログインのみが許可されているため、外部ホストからのログインができません。
このため、 'user'@'%' に変更して任意のインターフェースからのログインを受け付ける必要があります。

sudo mysql コマンドにより、rootユーザーとしてMySQLサーバーにログインし、 RENAME USER 'user'@'localhost' TO 'user'@'%'; とSQLクエリを実行することで、外部ホストからのログインを許可することができます。

採点基準

  • 加点系
    • Routerに設定されたfirewallの問題を解決している
      • +40点
    • Serverのufwについての問題を解決している
      • +40点
    • MySQLプロセスのBINDについての問題を解決している
      • +40点
    • MySQLのuserユーザーのHostについての問題を解決している
      • +40点
    • PCで mysql -h 172.16.2.128 -u user -pictsc2022 とコマンドを打つとログインできる
      • +40点
  • 減点系
    • Routerでeth1にfirewallが適用されていない
      • -40点
    • RouterのファイアウォールでTCP/UDP3306を許可する設定以外を追加されている
      • -20点
    • RouterのファイアウォールでTCP/UDP3306を許可する設定が、 172.16.2.128 以外にも適用されている
      • -10点
    • Serverのufwがdisable
      • -40点
    • ServerのufwでTCP22, TCP3306以外が許可されている
      • -20点
    • MySQLのuserを作成し直した(旨が回答に記載されている)
      • -35点
    • /etc/hosts で localhostの紐づけを変更した
      • -35点

講評

この問題は4つの原因が複合して発生したものであり、4つ全てを解決しないと満点解答とならない問題でした。
ただし、個々の問題自体はそれほど難しいものではないため、全体としてもあまり難しくはない問題と考えて出題しました。
しかし、満点解答を提出したチームが4チームのみと、全体を通して2番目に満点解答が少ない問題となりました。

問題文に「別プロダクトで使用するマシンにセキュリティホールを作った日には、始末書どころでは済まされない。」、前提条件に「172.16.2.0/24 のネットワークにはServer以外にも別プロダクトのホストが存在する」という記述を、競技2日目に追記しました。
これは、「RouterのファイアウォールでTCP/UDP3306を許可する設定が、172.16.2.128 以外にも適用されている」場合減点対象となることを暗示したものでしたが、この点を除いて正答であるチームが他に4チーム存在しました。
この点による減点を除けば、8チームがおおよそ正答に近い解答を提出しているため、想定通りの難易度だったといえます。

socatをServerで実行し、TCP3306ポートをUNIXソケットである mysqld.sock と紐づけることでlocalhostとして認識させるという解答がありました。
想定していた解法ではありませんでしたが制約には違反していないため、MySQLプロセスのBINDアドレスやMySQLのuserユーザーのHostに関する問題の解決方法として、以上の解答を認めました。